﻿using System;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using NPOI.Util;
using System.IO;
using System.Data;
using System.Diagnostics.Contracts;
using Coldairarrow.Util.Excel.Model;

namespace Coldairarrow.Util.Excel.Util
{
    public class ExcelUtil
    {
        public ExcelUtil()
        {
        }

        /// <summary>
        /// Excel转DataTable
        /// </summary>
        /// <param name="filePath">excel文件路径</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string filePath)
        {
            DataTable dt = new DataTable();
            using (FileStream fsRead = System.IO.File.OpenRead(filePath))
            {
                IWorkbook wk = null;
                //获取后缀名
                string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
                //判断是否是excel文件
                if (extension == ".xlsx" || extension == ".xls")
                {
                    //判断excel的版本
                    if (extension == ".xlsx")
                    {
                        wk = new XSSFWorkbook(fsRead);
                    }
                    else if (extension == ".xls")
                    {
                        wk = new HSSFWorkbook(fsRead);
                    }

                    //获取第一个sheet
                    ISheet sheet = wk.GetSheetAt(0);
                    //获取第一行为标题
                    IRow headrow = sheet.GetRow(0);

                    //创建列
                    for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
                    {
                        ICell cell = headrow.GetCell(i);
                        if (cell.ToString().Trim() != null && cell.ToString().Trim() != string.Empty)
                        {
                            dt.Columns.Add(cell.ToString());
                        }
                        else
                        {
                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));//标题没有名字的添加名字
                        }

                    }
                    int CountRow = sheet.LastRowNum + 1;//获取总行数
                    //去掉非空数据集合
                    List<IRow> rows;
                    rows = ExcelUtil.GetHeadNotEmptyRows(1, CountRow, sheet);
                    //读取每行,从第二行起
                    for (int r = 1; r <= rows.Count; r++)
                    {
                        DataRow dr = dt.NewRow();
                        //获取当前行
                        IRow row = sheet.GetRow(r);

                        int recordNullCount = 0;

                        //读取每列
                        for (int j = 0; j < headrow.Cells.Count; j++)
                        {
                            ICell cell = row.GetCell(j); //一个单元格
                            if (cell == null)
                            {
                                dr[j] = "";
                                continue;
                            }
                            if (cell.IsMergedCell && r > 1)  //检测列的单元格是否合并
                            {
                                var cellValue = GetCellValue(cell);
                                if (string.IsNullOrEmpty(cellValue))
                                {
                                    dr[j] = dt.Rows[r - 2][j];
                                }
                                else
                                {
                                    dr[j] = cellValue; //获取单元格的值
                                }
                            }
                            else
                            {
                                dr[j] = GetCellValue(cell); //获取单元格的值
                            }
                            if (dr[j].ToString() == null || dr[j].ToString() == "")//全为空则不取
                            {
                                recordNullCount += 1;
                            }
                            if (dr[j].ToString() == "合计")//如果是统计行就直接break掉
                            {
                                recordNullCount = dt.Columns.Count;
                                break;
                            }
                        }
                        if (recordNullCount == dt.Columns.Count)
                        {
                            break;
                        }
                        else
                        {
                            dt.Rows.Add(dr); //把每行追加到DataTable
                        }
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 对单元格进行判断取值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell, CellType cType = CellType.Blank)
        {
            if (cell == null)
                return string.Empty;
            if (cType == CellType.Blank)
                cType = cell.CellType;
            switch (cType)
            {
                case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
                    return string.Empty;
                case CellType.Boolean: //bool类型
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric: //数字类型
                    cell.SetCellType(CellType.Numeric);
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        if (cell.DateCellValue.ToString("yyyy") == "1899")
                        {
                            return cell.DateCellValue.ToString("HH:mm"); //如果只有具体时间无日期
                        }
                        if (cell.DateCellValue.ToString("HH:mm:ss") == "00:00:00")
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd"); //如果只有日期无具体时间
                        }
                        else
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); //如果日期和时间都是具体数值
                        }

                    }
                    else //其它数字
                    {
                        return cell.NumericCellValue.ToString();
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();//
                case CellType.String: //string 类型
                    {
                        cell.SetCellType(CellType.String);
                        return cell.StringCellValue;
                    }

                case CellType.Formula: //带公式类型
                    return GetCellValue(cell, cell.CachedFormulaResultType);
            }
        }

        /// <summary>
        /// 取得工作薄
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>

        public static IWorkbook GetWorkbook(FileStream file)
        {
            IWorkbook workbook = null;
            try
            {
                workbook = WorkbookFactory.Create(file);
            }
            catch (Exception ex)
            {
                throw new Exception("上传文件为非标准Excel文件，请重新选择文件！",ex);
            }
            return workbook;
        }

        /// <summary>
        /// 取得工作薄
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static IWorkbook GetWorkbook(string path)
        {
            IWorkbook workbook = null;
            try
            {
                workbook = WorkbookFactory.Create(path);
            }
            catch (Exception ex)
            {
                throw new Exception("上传文件为非标准Excel文件，请重新选择文件！", ex);
            }
            return workbook;
        }


        /// <summary>
        /// 取得所有非空的行集合
        /// </summary>
        /// <param name="startNum"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static List<IRow> GetNotEmptyRows(int startNum, ISheet sheet)
        {
            List<IRow> rows = new List<IRow>();

            for (int i = startNum; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i);
                if (row != null)
                {
                    //if (row.GetCell(0) != null && row.GetCell(0).CellType == CellType.String && (row.GetCell(0).StringCellValue.Contains("规则说明") || row.GetCell(0).StringCellValue.Contains("填表说明")))
                    //{
                    //    break;
                    //}
                   // var aa = row.GetCell(i).IsNullOrEmpty();
                    if (row.GetCell(0).IsNullOrEmpty()||row.GetCell(3).IsNullOrEmpty()||row.GetCell(15).IsNullOrEmpty())
                    {
                        break;
                    }

                    rows.Add(row);
                }
            }

            return rows;
        }

        /// <summary>
        /// 判断是否空行
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public static bool IsEmtyRow(IRow row)
        {
            for (int i = 0; i < row.LastCellNum; ++i)
            {
                if (!String.IsNullOrEmpty(GetCellStringValue(row.GetCell(i))))
                {
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// 取得非空的行集合
        /// </summary>
        /// <param name="startNum"></param>
        /// <param name="endNum"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static List<IRow> GetHeadNotEmptyRows(int startNum, int endNum, ISheet sheet)
        {
            List<IRow> rows = new List<IRow>();
            for (int i = startNum; i <= endNum; ++i)
            {
                IRow row = sheet.GetRow(i);
                if (row != null)
                {
                    bool isEmpty = true;

                    for (int j = 0; j < row.LastCellNum; ++j)
                    {
                        ICell cell = row.GetCell(j);
                        //if (cell != null && !string.IsNullOrEmpty(GetCellStringValue(cell)))
                        //{
                        //    isEmpty = false;
                        //    break;
                        //}
                        if (cell != null && !string.IsNullOrWhiteSpace(GetCellStringValue(cell)))
                        {
                            isEmpty = false;
                            break;
                        }
                    }
                    if (!isEmpty)
                    {
                        rows.Add(row);
                    }
                }
            }
            return rows;
        }

        /// <summary>
        ///取得单元格值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static String GetCellStringValue(ICell cell)
        {
            if (null == cell)
            {
                return "";
            }
            else
            {
                String rValue = "";
                switch (cell.CellType)
                {
                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            rValue = String.Format("%tF", cell.DateCellValue);
                        }
                        else
                        {
                            rValue = cell.NumericCellValue.ToString();
                            if (rValue.Contains(".") && rValue.EndsWith(".0"))
                            {
                                rValue = rValue.Substring(0, rValue.IndexOf("."));
                            }
                        }
                        break;
                    case CellType.String:
                        rValue = cell.StringCellValue;
                        break;
                    case CellType.Formula:
                        if (CellType.String == cell.CachedFormulaResultType)
                        {
                            rValue = cell.StringCellValue;
                        }
                        else if (CellType.Numeric == cell.CachedFormulaResultType)
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                rValue = String.Format("%tF", cell.DateCellValue);
                            }
                            else
                            {
                                rValue = cell.NumericCellValue.ToString();
                                if (rValue.Contains(".") && rValue.EndsWith(".0"))
                                {
                                    rValue = rValue.Substring(0, rValue.IndexOf("."));
                                }
                            }
                        }
                        break;
                    case CellType.Blank:
                        rValue = "";
                        break;
                }

                return rValue;
            }
        }

        /// <summary>
        /// 判断是否是合并单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static bool IsMergedRegion(ISheet sheet, ICell cell)
        {
            if (sheet != null && cell != null)
            {
                int sheetmergerCount = sheet.NumMergedRegions;

                for (int i = 0; i < sheetmergerCount; ++i)
                {
                    CellRangeAddress ca = sheet.MergedRegions[i];
                    int firstC = ca.FirstColumn;
                    int lastC = ca.LastColumn;
                    int firstR = ca.FirstRow;
                    int lastR = ca.LastRow;
                    if (cell.ColumnIndex <= lastC && cell.ColumnIndex >= firstC && cell.RowIndex <= lastR && cell.RowIndex >= firstR)
                    {
                        return true;
                    }
                }

                return false;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 取得合并单元格的值
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string GetMergedRegionValue(ISheet sheet, ICell cell)
        {
            int sheetmergerCount = sheet.NumMergedRegions;

            for (int i = 0; i < sheetmergerCount; ++i)
            {
                CellRangeAddress ca = sheet.MergedRegions[i];
                int firstC = ca.FirstColumn;
                int lastC = ca.LastColumn;
                int firstR = ca.FirstRow;
                int lastR = ca.LastRow;
                if (cell.ColumnIndex <= lastC && cell.ColumnIndex >= firstC && cell.RowIndex <= lastR && cell.RowIndex >= firstR)
                {
                    IRow fRow = sheet.GetRow(firstR);
                    ICell fCell = fRow.GetCell(firstC);
                    return GetCellStringValue(fCell);
                }
            }

            return "";
        }

        /// <summary>
        /// 根据sheet名取得sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="validataName"></param>
        /// <returns></returns>
        public static ISheet GetSheetByName(IWorkbook workbook, String validataName)
        {
            for (int i = 0; i < workbook.NumberOfSheets; ++i)
            {
                if (workbook.GetSheetAt(i).SheetName.Equals(validataName))
                {
                    return workbook.GetSheetAt(i);
                }
            }

            return null;
        }

        /// <summary>
        /// 设置合并单元格样式
        /// </summary>
        /// <param name="cloneStyle"></param>
        /// <param name="cra"></param>
        /// <param name="sheet"></param>
        public static void SetBodyStyle(ICellStyle cloneStyle, CellRangeAddress cra, ISheet sheet)
        {
            IRow rowtemp = null;
            ICell celltemp = null;

            for (int i = cra.FirstRow; i <= cra.LastRow; ++i)
            {
                rowtemp = CellUtil.GetRow(i, sheet);
                for (int j = cra.FirstColumn; j <= cra.LastColumn; ++j)
                {
                    celltemp = rowtemp.GetCell(j);
                    if (celltemp == null)
                    {
                        celltemp = rowtemp.CreateCell(j);
                    }
                    celltemp.CellStyle = cloneStyle;
                }
            }
        }

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="isHeadStyle">是否头部样式</param>
        /// <returns></returns>
        public static ICellStyle SetCellStyle(IWorkbook workbook, bool isHeadStyle)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BottomBorderColor = IndexedColors.Black.Index;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.LeftBorderColor = IndexedColors.Black.Index;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.RightBorderColor = IndexedColors.Black.Index;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.TopBorderColor = IndexedColors.Black.Index;
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.WrapText = true;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 10;
            if (isHeadStyle)
            {
                font.IsBold = true;
            }
            cellStyle.SetFont(font);
            return cellStyle;
        }

        /// <summary>
        /// 取得excel标题最大行数
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static int GetHeadMaxRowNum(DataTable dataTable)
        {
            int maxRowNum = 0;
            if (dataTable != null)
            {
                for (int i = 0; i < dataTable.Columns.Count; ++i)
                {
                    DataColumn dataColumn = dataTable.Columns[i];
                    if (dataColumn != null && !string.IsNullOrEmpty(dataColumn.ColumnName))
                    {
                        //string[] value = dataColumn.ColumnName.Split("\\.");
                        string[] value = dataColumn.ColumnName.Split(new char[] { '.' });
                        if (value != null && value.Length >= 1 && value.Length > maxRowNum)
                        {
                            maxRowNum = value.Length;
                        }
                    }
                }
            }
            return maxRowNum;
        }

        /// <summary>
        /// 取得合并单元格列数
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static int GetMergeColumn(ICell cell, ISheet sheet)
        {
            int mergeColumn = 1;
            int lastCellNum = sheet.GetRow(cell.RowIndex).LastCellNum;
            for (int i = cell.ColumnIndex; i < lastCellNum; i++)
            {
                ICell cell2 = sheet.GetRow(cell.RowIndex).GetCell(i + 1);
                if(cell2 ==null || string.IsNullOrEmpty(GetCellStringValue(cell2)))
                {
                    return mergeColumn;
                }

                if(GetCellStringValue(cell2).Equals(GetCellStringValue(cell)))
                {
                    IRow subRow = sheet.GetRow(cell.RowIndex - 1);
                    if(subRow == null)
                    {
                        mergeColumn++;
                        cell2.SetCellValue("");
                        continue;
                    }

                    ICell subCell = subRow.GetCell(cell.ColumnIndex);
                    ICell subCell2 = subRow.GetCell(i + 1);
                    if ((!string.IsNullOrEmpty(GetCellStringValue(subCell)) || string.IsNullOrEmpty(GetCellStringValue(subCell2)))
                       && (string.IsNullOrEmpty(GetCellStringValue(subCell)) || string.IsNullOrEmpty(GetCellStringValue(subCell2)) || GetCellStringValue(subCell).Equals(GetCellStringValue(subCell2))))
                    {
                        mergeColumn++;
                        cell2.SetCellValue("");
                        continue;
                    }
                    break;
                }
            }
            return mergeColumn;
        }

        /// <summary>
        /// 取得合并单元格行数
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="sheet"></param>
        /// <param name="maxRowNum"></param>
        /// <returns></returns>
        public static int GetMergeRow(ICell cell, ISheet sheet, int maxRowNum)
        {
            int mergerRow = 1;
            for (int i = cell.RowIndex; i < maxRowNum-1; i++)
            {
                ICell cell2 = sheet.GetRow(i + 1).GetCell(cell.ColumnIndex);
                if(cell2 !=null && !string.IsNullOrEmpty(GetCellStringValue(cell2)))
                {
                    break;
                }
                mergerRow++;
            }
            return mergerRow;
        }

        /// <summary>
        /// 验证excel
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="modelWorkbook"></param>
        /// <param name="parseExcelRuleApp"></param>
        public static void ValidateExcel(IWorkbook workbook, IWorkbook modelWorkbook, ParseExcelRuleUtil parseExcelRuleApp)
        {
            for (int index = 0; index < modelWorkbook.NumberOfSheets; index++)
            {
                ISheet modelSheet = modelWorkbook.GetSheetAt(index);
                if (modelSheet != null)
                {
                    SheetRule rule = parseExcelRuleApp.GetRuleBySheetName(modelSheet.SheetName);
                    if (rule == null)
                    {
                        rule = parseExcelRuleApp.GetRuleBySheetIndex(index);
                        if (rule == null)
                        {
                            continue;
                        }
                    }
                    if (rule.IsAnalyzeHead)
                    {
                        ISheet sheet = workbook.GetSheet(modelSheet.SheetName);
                        if (sheet == null)
                        {
                            throw new Exception("缺失名称为:【" + modelSheet.SheetName + "】的Sheet页!");
                            //return "缺失名称为:【" + modelSheet.SheetName + "】的Sheet页!";
                           // break;
                        }

                        int rowHeadNum = rule.RowHeadNum;
                        int rowHeadEndNum = rule.RowHeadEndNum;

                        for (int i = rowHeadNum; i <= rowHeadEndNum; ++i)
                        {
                            IRow modelRow = modelSheet.GetRow(i);
                            if (modelRow != null)
                            {
                                IRow row = sheet.GetRow(i);
                                if (row == null)
                                {
                                    throw new Exception("导入的模板不合法!sheet页:" + modelSheet.SheetName + "，缺少必要的行列头：");
                                }

                                for (int j = 0; j < modelRow.LastCellNum; ++j)
                                {
                                    ICell modelCell = modelRow.GetCell(j);
                                    ICell cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        throw new Exception("导入的模板不合法!sheet页:" + modelSheet.SheetName + "，缺少必要的行列头：" + GetCellStringValue(modelCell));
                                    }

                                    String modelHeadName = GetCellStringValue(modelCell).Trim();
                                    String headName = GetCellStringValue(cell).Trim();
                                    if (!modelHeadName.Equals(headName))
                                    {
                                        throw new Exception("导入的模板不合法!sheet页:" + modelSheet.SheetName + "第" + (modelCell.RowIndex + 1) + "行" + (modelCell.ColumnIndex + 1) + "列单元格内容与模板不符合!");
                                    }
                                }
                            }
                        }
                    }
                }
            }

          
        }

        /// <summary>
        /// 取得excel的sheet名列表
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public static string[] GetSheetNames(IWorkbook workbook)
        {
            List<string> sheetNames = new List<string>();
            for (int i = 0; i < workbook.NumberOfSheets; ++i)
            {

                sheetNames.Add(workbook.GetSheetAt(i).SheetName);
            }

            return sheetNames.ToArray();
        }
    }
}
